﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetRightInTeamToHandleClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetRightInTeamToHandleClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetRightInTeamToHandleClass]  
        @ClassID int,
        @UserName nvarchar(50),
        @Act_ID int,
        @AllowAccess bit output
/*

======================================================================
功能:    得到项目组中@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int             :    组（类）ID
    @UserName nvarchar(255)        :    用户名
    @Act_ID int            :    权利代号
    @Inherit int =1            :    是否继承

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON

SET @AllowAccess = 0;

--得到所有被访问组的父组
DECLARE @pteam TABLE (pid int not null);
with Class_ParentTree ( ClassID )
as
(
    select @ClassID

    union all    

    select c.ClassParentID
    from Class_ParentTree p inner join dbo.UDS_Class c on p.ClassID = c.ClassID and c.ClassID <> c.ClassParentID
)
insert into @pteam ( pid )
select ClassID
from Class_ParentTree;


--以下判断是判断用户是否再任何一个能访问此类的父类的组中(能访问父类就能访问子类)

--得到所有父组中拥有此权利的组ID
DECLARE @pAssignedTeam TABLE (TeamID int not null);
INSERT INTO @pAssignedTeam (TeamID)
    SELECT A.team_id 
        FROM 
            dbo.UDS_Assign_Rule a,
            @pteam b
        WHERE
            a.act_id = @Act_id
            and a.based_on = 2 /* team */
            and a.Team_ID = b.pid;


--返回用户是否在有权利的组中（组成员都具有此权利，不管是组长还是成员）
IF EXISTS (
    SELECT 1
    FROM 
        dbo.uds_staff 
    WHERE
        staff_name = @username
        and
        (
          staff_id in (
                SELECT staff_id 
                    FROM 
                        dbo.uds_staff_in_team a,
                        @pAssignedTeam b 
                    WHERE a.team_id = b.teamid
                  ) 
          OR 
--得到组角色的权利
          staff_id in (
                SELECT staff_id
                    FROM
                        dbo.uds_staff_in_team a,
                        dbo.uds_activity b
                    WHERE
                        a.member_type = b.act_id
                        and b.proc_id = @Act_id
                        and A.team_id in (SELECT pid FROM @pteam) 
                   )
        )
)
BEGIN
    SET @AllowAccess = 1;
END

END
GO
